﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Xml;
using System.Configuration;

namespace TaxService
{
    internal class Invoices
    {
        /// <summary>
        /// 获取发票信息
        /// </summary>
        /// <returns></returns>
        public string InvoicesXML()
        {
            #region 获取模板数据
            string Path = ConfigurationManager.AppSettings["TemplateFilePath"].ToString() + @"\InvoiceTemplate.xml";
            XmlDocument Invoice = new XmlDocument();
            Invoice.Load(Path);
            #endregion
            string TaxArea = ConfigurationManager.AppSettings["TaxArea"].ToString(); //开票单位：BJ SH GZ CQ
            if (!new List<string>() { "BJ", "SH", "GZ","CQ" }.Contains(TaxArea))
            {
                throw new Exception("开票单位配置异常，请重新配置");
            }
            XmlDocument tempxml = null;
            XmlNode newNode = null;
            //表头信息
            string HeadXml = Invoice.SelectSingleNode(".//T_FPHDR_OUT").OuterXml;
            //行信息
            string LineXml = Invoice.SelectSingleNode(".//T_FPITEM_OUT").OuterXml;
            #region 移除空节点
            Invoice.SelectSingleNode("ROOT").RemoveChild(Invoice.SelectSingleNode(".//T_FPHDR_OUT"));
            Invoice.SelectSingleNode("ROOT").RemoveChild(Invoice.SelectSingleNode(".//T_FPITEM_OUT"));
            #endregion
            #region 获取表头数据
            var db = Database.DatabaseFactory.CreateDatabase();
            db.BeginTransaction();
            string SQL = string.Empty;
            string OrderNo = string.Empty;
            #region SQL
            SQL = string.Format(@"SELECT T0.DocEntry INCOME_INVOICE_NUM
                    ,U_ApplyDate INCOME_INVOICE_DATE
                    ,U_CardCode CUSTOM_NUM
                    ,U_CardName CUSTOM_NAME
                    ,'0' IS_RED
                    ,U_Invoice INVOICE_TITLE
                    ,''STATION_NUM
                    ,'' MACHINE_NUM
                    ,U_Remarks MEMO
                    ,Case When U_InvoiceTaxType = 'P' Then '0' Else '2' End TAXTYPE
                    ,T0.U_TaxpayerNo TAXNO
                    ,T0.U_TaxpayerAddress TAXADDRESS
                    ,T0.U_TaxpayerBank BANK
                    ,T0.U_TaxpayerCardNo BANK_ACCOUNT
                    ,'' HELP_INFO
                    ,T0.U_InvCurr CURRENCY_TYPE
                    ,T0.U_Rate CURRENCY_RATE
                    ,T3.Amount CURRENCY_AMOUNT
                    ,Case When T1.ShipType = '1' Then '一般纳税人' Else '小规模' End CUSTOM_TYPE
                FROM [@AVA_OINV] T0 
                    INNER JOIN OCRD T1 ON T0.U_CardCode = T1.CardCode
                    INNER JOIN [@AVA_INV1] T2 ON T0.DocEntry = T2.DocEntry
                    INNER JOIN (SELECT DocEntry,SUM(IsnulL(U_Amount,0)) Amount FROM [@AVA_INV2] Group By DocEntry) T3 ON T0.DocEntry = T3.DocEntry
                Where T0.U_Status = 'O'  AND T0.U_TaxArea=N'{0}'
                UNION ALL
                SELECT T0.DocEntry INCOME_INVOICE_NUM
                    ,U_ApplyDate INCOME_INVOICE_DATE
                    ,U_CardCode CUSTOM_NUM
                    ,U_CardName CUSTOM_NAME
                    ,'1' IS_RED
                    ,U_Invoice INVOICE_TITLE
                    ,''STATION_NUM
                    ,'' MACHINE_NUM
                    ,U_Remarks MEMO
                    ,Case When U_InvoiceTaxType = 'P' Then '0' Else '2' End TAXTYPE
                    ,T0.U_TaxpayerNo TAXNO
                    ,T0.U_TaxpayerAddress TAXADDRESS
                    ,T0.U_TaxpayerBank BANK
                    ,T0.U_TaxpayerCardNo BANK_ACCOUNT
                    ,'' HELP_INFO
                    ,T0.U_InvCurr CURRENCY_TYPE
                    ,T0.U_Rate CURRENCY_RATE
                    ,T3.Amount CURRENCY_AMOUNT
                    ,Case When T1.ShipType = '1' Then '一般纳税人' Else '小规模' End CUSTOM_TYPE
                FROM [@AVA_OINV] T0 
                    INNER JOIN OCRD T1 ON T0.U_CardCode = T1.CardCode
                    INNER JOIN [@AVA_INV1] T2 ON T0.DocEntry = T2.DocEntry
                    INNER JOIN (SELECT DocEntry,SUM(IsnulL(U_Amount,0)) Amount FROM [@AVA_INV1] Group By DocEntry) T3 ON T0.DocEntry = T3.DocEntry
                Where T0.U_Status = 'Q' And T0.Canceled = 'Y' AND T0.U_TaxArea=N'{0}'
", TaxArea);
            #endregion
            IDataReader reader = db.ExceuteReader(SQL);
            DataTable Headdt = new DataTable();
            Headdt.Load(reader);
            reader.Close();
            #endregion
            if (Headdt.Rows.Count <= 0)
            {
                Invoice.SelectSingleNode("ROOT").InnerText = "没有发票信息";
            }
            else
            {
                foreach (DataRow row in Headdt.Rows)
                {
                    #region Head
                    tempxml = new XmlDocument();
                    tempxml.LoadXml(HeadXml);
                    OrderNo += row["INCOME_INVOICE_NUM"].ToString().Trim() + "_";
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/INCOME_INVOICE_NUM").InnerText = row["INCOME_INVOICE_NUM"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/INCOME_INVOICE_DATE").InnerText = row["INCOME_INVOICE_DATE"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/CUSTOM_NUM").InnerText = row["CUSTOM_NUM"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/IS_RED").InnerText = row["IS_RED"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/INVOICE_TITLE").InnerText = row["INVOICE_TITLE"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/STATION_NUM").InnerText = row["STATION_NUM"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/MACHINE_NUM").InnerText = row["MACHINE_NUM"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/MEMO").InnerText = row["MEMO"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/TAXTYPE").InnerText = row["TAXTYPE"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/CUSTOM_NAME").InnerText = row["CUSTOM_NAME"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/TAXNO").InnerText = row["TAXNO"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/TAXADDRESS").InnerText = row["TAXADDRESS"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/BANK").InnerText = row["BANK"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/BANK_ACCOUNT").InnerText = row["BANK_ACCOUNT"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/HELP_INFO").InnerText = row["HELP_INFO"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/CURRENCY_TYPE").InnerText = row["CURRENCY_TYPE"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/CURRENCY_RATE").InnerText = row["CURRENCY_RATE"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/CURRENCY_AMOUNT").InnerText = row["CURRENCY_AMOUNT"].ToString().Trim();
                    tempxml.SelectSingleNode("//T_FPHDR_OUT/CUSTOM_TYPE").InnerText = row["CUSTOM_TYPE"].ToString().Trim();
                    newNode = Invoice.SelectSingleNode("ROOT").OwnerDocument.ImportNode(tempxml.LastChild, true);
                    Invoice.SelectSingleNode("ROOT").AppendChild(newNode);
                    #endregion
                    #region 获取行数据
                    #region lines SQL
                    SQL = @"SELECT T1.DocEntry INCOME_INVOICE_NUM
                        ,T1.LineId INCOME_INVOICE_LINE_ID
                        ,T1.U_CargoCode GOODS_NUM
                        ,T1.U_CargoName SHORT_NAME
                        ,T1.U_Unit UNIT
                        ,U_Stlye SPEC
                        ,U_Quantity QUANTITY
                        ,U_Price PRICE                        
                        ,Case T0.U_InvoiceTaxType
                            When 'H' Then 0.17
                            When 'G' Then 0.06
                            When 'P' Then 0.06
                            When 'S' Then 0.03
                            When 'F' Then 0.00
                        End TAX_RATE
                        ,T1.U_Amount TOTAL_TAX_PRICE
                        ,0 DISCOUNT_PRICE
                        ,T0.U_Rate EXCHANGE_RATE
                        ,0 ITEM_TYPE /*Case When T1.U_IsTax = 'Y' Then 0 Else 1 End*/
                    FROM [@AVA_OINV] T0 
                        INNER JOIN [@AVA_INV1] T1 ON T0.DocEntry = T1.DocEntry
                    Where T0.U_Status = 'O' And T0.DocEntry = {0} 
                    UNION ALL
                    SELECT T1.DocEntry INCOME_INVOICE_NUM
                        ,T1.LineId INCOME_INVOICE_LINE_ID
                        ,T1.U_CargoCode GOODS_NUM
                        ,T1.U_CargoName SHORT_NAME
                        ,T1.U_Unit UNIT
                        ,U_Stlye SPEC
                        ,-U_Quantity QUANTITY
                        ,U_Price PRICE
                        ,Case T0.U_InvoiceTaxType
                            When 'H' Then 0.17
                            When 'G' Then 0.06
                            When 'P' Then 0.06
                            When 'S' Then 0.03
                            When 'F' Then 0.00
                        End TAX_RATE
                        ,-(T1.U_TaxAmount+T1.U_Amount) TOTAL_TAX_PRICE
                        ,-T1.U_Amount DISCOUNT_PRICE
                        ,T0.U_Rate EXCHANGE_RATE
                        ,0 ITEM_TYPE
                    FROM [@AVA_OINV] T0 
                        INNER JOIN [@AVA_INV1] T1 ON T0.DocEntry = T1.DocEntry
                    Where T0.U_Status = 'Q' And T0.Canceled = 'Y' And T0.DocEntry = {0}";
                    #endregion
                    reader = db.ExceuteReader(string.Format(SQL, row["INCOME_INVOICE_NUM"].ToString().Trim()));
                    DataTable Linedt = new DataTable();
                    Linedt.Load(reader);
                    reader.Close();
                    #endregion
                    foreach (DataRow line in Linedt.Rows)
                    {
                        #region line
                        tempxml = new XmlDocument();
                        tempxml.LoadXml(LineXml);
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/INCOME_INVOICE_NUM").InnerText = line["INCOME_INVOICE_NUM"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/INCOME_INVOICE_NUM").InnerText = line["INCOME_INVOICE_NUM"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/INCOME_INVOICE_LINE_ID").InnerText = line["INCOME_INVOICE_LINE_ID"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/GOODS_NUM").InnerText = line["GOODS_NUM"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/SHORT_NAME").InnerText = line["SHORT_NAME"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/UNIT").InnerText = line["UNIT"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/SPEC").InnerText = line["SPEC"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/QUANTITY").InnerText = line["QUANTITY"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/PRICE").InnerText = line["PRICE"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/TAX_RATE").InnerText = line["TAX_RATE"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/TOTAL_TAX_PRICE").InnerText = line["TOTAL_TAX_PRICE"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/DISCOUNT_PRICE").InnerText = line["DISCOUNT_PRICE"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/EXCHANGE_RATE").InnerText = line["EXCHANGE_RATE"].ToString().Trim();
                        tempxml.SelectSingleNode("//T_FPITEM_OUT/ITEM_TYPE").InnerText = line["ITEM_TYPE"].ToString().Trim();
                        newNode = Invoice.SelectSingleNode("ROOT").OwnerDocument.ImportNode(tempxml.LastChild, true);
                        Invoice.SelectSingleNode("ROOT").AppendChild(newNode);
                        #endregion
                    }
                }
                SQL = string.Format(@"UPDATE T0 SET T0.U_Status = 'G'
                    FROM [@AVA_OINV] T0 
                        INNER JOIN OCRD T1 ON T0.U_CardCode = T1.CardCode
                    Where T0.U_Status = 'O'  AND T0.U_TaxArea=N'{0}';
                    UPDATE T0 SET T0.U_Status = 'G'
                    FROM [@AVA_OINV] T0 
                        INNER JOIN OCRD T1 ON T0.U_CardCode = T1.CardCode
                    Where T0.U_Status = 'Q' And T0.Canceled = 'Y'  AND T0.U_TaxArea=N'{0}' ", TaxArea);
                db.ExceuteNonQuery(SQL);
                db.EndTranscation(Database.TransactionOptions.Commit);
                db.CloseConnection();
                Invoice.Save(ConfigurationManager.AppSettings["InvoiceFilePath"].ToString() + @"\Invoice_" + OrderNo + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xml");
            }
            return Invoice.OuterXml.ToString();
        }
        /// <summary>
        /// 获取发票信息
        /// </summary>
        /// <param name="DocEntry">单据编号</param>
        /// <param name="StartDate">开票日期起</param>
        /// <param name="EndDate">开票日期止</param>
        /// <param name="InvoiceTitle">发票抬头</param>
        /// <param name="StationNo">开票站</param>
        /// <param name="MachineNo">机器编号</param>
        /// <param name="OrderCounter">订单数量</param>
        /// <returns></returns>
        public string InvoicesXML(string DocEntry, DateTime StartDate, DateTime EndDate, string InvoiceTitle, string StationNo, string MachineNo, int OrderCounter)
        {
            return null;
        }
        /// <summary>
        /// 反馈的发票信息
        /// 1、正常的发票
        /// 2、作废的发票
        /// </summary>
        /// <param name="ReturnInvoiceXml"></param>
        /// <returns></returns>
        public string ReturnInvoice(string ReturnInvoiceXml)
        {
            string Path = ConfigurationManager.AppSettings["TemplateFilePath"].ToString() + @"\ResultTemplate.xml";
            XmlDocument result = new XmlDocument();
            result.Load(Path);

            XmlDocument invoice = new XmlDocument();
            try
            {
                invoice.LoadXml(ReturnInvoiceXml);
            }
            catch
            {
                result.SelectSingleNode("//CODE").InnerText = "1";
                result.SelectSingleNode("//MESSAGE").InnerText = "传入的参数数据不是有效的XML格式字符串";
                return result.OuterXml;
            }
            var db = Database.DatabaseFactory.CreateDatabase();
            db.BeginTransaction();
            try
            {
                string SQL = string.Empty;
                foreach (XmlNode node in invoice.SelectNodes("//T_FPHDR_OUT"))
                {
                    string DocEntry = node.SelectSingleNode(".//INCOME_INVOICE_NUM").InnerText;//SAP单据号
                    bool IsPart = false;
                    if (DocEntry.Split('.').Length > 1)
                        IsPart = true;
                    else
                        IsPart = false;
                    DocEntry = DocEntry.Split('.')[0];
                    #region 异常
                    if (InvoiceExists(db, DocEntry) == false)
                    {
                        result.SelectSingleNode("//CODE").InnerText = "1";
                        result.SelectSingleNode("//MESSAGE").InnerText = DocEntry + " 发票号码不是SAP中有效的单据。（不是SAP传给金额的）";
                        return result.OuterXml;
                    }
                    #endregion
                    string InvoiceNo = node.SelectSingleNode(".//TAX_INVOICE_NUM").InnerText;//发票号
                    #region 判断发票号是否存在
                    SQL = "SELECT U_InvoiceNum FROM [@AVA_OINV] Where DocEntry = " + DocEntry;
                    string OInvoiceNo = db.ExceuteScalar(SQL).ToString();
                    IList<string> Invoices = OInvoiceNo.Split(';').ToList();
                    bool IsInvoice = false;
                    if (Invoices.Contains(InvoiceNo))
                        IsInvoice = true;
                    else
                        IsInvoice = false;
                    #endregion
                    string Amount = node.SelectSingleNode(".//TOTAL_MONEY").InnerText;//发票金税
                    string InvoiceStatus = node.SelectSingleNode(".//INVOICE_STATU").InnerText; //发票状态 
                    Log.Logger.Writer("回写状态：" + InvoiceStatus + "\r\n" + IsInvoice);
                    switch (InvoiceStatus.Trim())
                    {
                        case "0":
                            if (IsInvoice == false)
                            {
                                #region 正常发票回写
                                if (IsPart)
                                    SQL = @"UPDATE [@AVA_OINV] 
                                        SET U_Status = 'C'
                                           ,U_InvoiceDate = GETDATE()
                                           ,U_InvoiceNum = (Case When ISNULL(U_InvoiceNum,'') = '' Then '' ELSE U_InvoiceNum + ';' End) + '{1}'
                                           ,U_InvoiceAmount = ISNULL(U_InvoiceAmount,0) + {2} 
                                   Where DocEntry = {0}
                                   UPDATE T2 
	                                    SET T2.U_InvoiceNO = (Case When ISNULL(T2.U_InvoiceNO,'') = '' Then '' Else T2.U_InvoiceNO + ';' End) + '{1}'
                                            ,T2.U_InvoiceAmount = ISNULL(T2.U_InvoiceAmount,0) + {2}
                                            ,T2.U_InvoiceData = Getdate()
                                            ,T2.U_Invoice = T0.U_Invoice                                            
                                    FROM [@AVA_OINV] T0
	                                    INNER JOIN [@AVA_INV2] T1 ON T0.DocEntry = T1.DocEntry
	                                    INNER JOIN ODRF T2 ON ((T1.U_Direction = 'I' And T2.ObjType = '13') Or (T1.U_Direction = 'O' And T2.ObjType = '18')) And T1.U_BaseEntry = T2.DocEntry
                                    Where T0.DocEntry = {0}	";
                                else
                                    SQL = @"UPDATE [@AVA_OINV] 
                                        SET U_Status = 'C'
                                           ,U_InvoiceDate = GETDATE()
                                           ,U_InvoiceNum = '{1}'
                                           ,U_InvoiceAmount =  {2} 
                                   Where DocEntry = {0}
                                   UPDATE T2 
	                                    SET T2.U_InvoiceNO =  '{1}'
                                            ,T2.U_InvoiceAmount = {2}
                                            ,T2.U_InvoiceData = Getdate()
                                            ,T2.U_Invoice = T0.U_Invoice                                            
                                    FROM [@AVA_OINV] T0
	                                    INNER JOIN [@AVA_INV2] T1 ON T0.DocEntry = T1.DocEntry
	                                    INNER JOIN ODRF T2 ON ((T1.U_Direction = 'I' And T2.ObjType = '13') Or (T1.U_Direction = 'O' And T2.ObjType = '18')) And T1.U_BaseEntry = T2.DocEntry
                                    Where T0.DocEntry = {0}	";
                                //更新表头的发票号码
                                db.ExceuteNonQuery(string.Format(SQL, DocEntry, InvoiceNo, Amount));
                                foreach (XmlNode childnode in invoice.SelectNodes("//T_FPITEM_OUT[INCOME_INVOICE_NUM='" + DocEntry + "']"))
                                {
                                    string LineId = childnode.SelectSingleNode(".//INCOME_INVOICE_LINE_ID").InnerText;
                                    string TaxAmount = childnode.SelectSingleNode(".//TAXES").InnerText;
                                    string NoTaxAmount = childnode.SelectSingleNode(".//NO_TAX_AMOUNT").InnerText;
                                    string AmountTotal = childnode.SelectSingleNode(".//TOTAL_TAX_PRICE").InnerText;
                                    if (IsPart)
                                    {
                                        SQL = @"UPDATE [@AVA_INV1] 
                                                SET 
                                                    U_Price = Case When ISNULL(U_TaxAmount,0) = 0 Then 0 Else ISNULL(U_Price,0) End + {3},
                                                    U_TaxAmount = Case When ISNULL(U_TaxAmount,0) = 0 Then 0 Else ISNULL(U_TaxAmount,0) End + {2},
                                                    U_Amount = Case When ISNULL(U_TaxAmount,0) = 0 Then 0 Else ISNULL(U_Amount,0) End + {3},
                                                    U_AmountTotal = Case When ISNULL(U_TaxAmount,0) = 0 Then 0 Else ISNULL(U_AmountTotal,0) End + {4} 
                                                Where DocEntry = {0} And LineId = {1}";
                                    }
                                    else
                                        SQL = @"UPDATE [@AVA_INV1] 
                                                SET 
                                                    U_Price = {3},
                                                    U_TaxAmount = {2},
                                                    U_Amount = {3},
                                                    U_AmountTotal = {4} 
                                                Where DocEntry = {0} And LineId = {1}";
                                    db.ExceuteNonQuery(string.Format(SQL, DocEntry, LineId, TaxAmount, NoTaxAmount, AmountTotal));
                                }
                                #endregion
                            }
                            break;
                        case "2":
                            #region 红字发票回写
                            //红字发票
                            SQL = @"UPDATE [@AVA_OINV] 
                                SET U_Status = 'P',Canceled = 'N',U_InvoiceDate = GETDATE(),U_InvoiceNum = '{1}',U_InvoiceAmount = {2},U_Comments = '红字发票'                                
                            Where DocEntry = {0}
                            UPDATE T2   
	                            SET T2.U_InvoiceAmount =''
                                    ,T2.U_InvoiceData = ''
                                    ,T2.U_Invoice = ''
                                    ,T2.U_InvoiceNO = ''
                            FROM [@AVA_OINV] T0
	                            INNER JOIN [@AVA_INV2] T1 ON T0.DocEntry = T1.DocEntry
	                            INNER JOIN ODRF T2 ON ((T1.U_Direction = 'I' And T2.ObjType = '13') Or (T1.U_Direction = 'O' And T2.ObjType = '18')) And T1.U_BaseEntry = T2.DocEntry
                            Where T0.DocEntry = {0}	";
                            //更新表头的发票号码
                            db.ExceuteNonQuery(string.Format(SQL, DocEntry, InvoiceNo, Amount));
                            #endregion
                            break;
                        case "1":
                            #region 作废发票回写
                            //作废发票
                            SQL = @"
                                   UPDATE T2 
	                                    SET T2.U_InvoiceAmount = Case When T2.U_InvoiceAmount - {2} >=0 Then T2.U_InvoiceAmount - {2} Else 0 End 
                                            ,T2.U_InvoiceData = Getdate()
                                            ,T2.U_InvoiceNO = ''                                
                                    FROM [@AVA_OINV] T0
	                                    INNER JOIN [@AVA_INV2] T1 ON T0.DocEntry = T1.DocEntry
	                                    INNER JOIN ODRF T2 ON ((T1.U_Direction = 'I' And T2.ObjType = '13') Or (T1.U_Direction = 'O' And T2.ObjType = '18')) And T1.U_BaseEntry = T2.DocEntry
                                    Where T0.U_Status = 'C' And T0.DocEntry = {0}	
                                    UPDATE T2 
	                                    SET T2.U_IsInvoice = 'N'                                
                                    FROM [@AVA_OINV] T0
	                                    INNER JOIN [@AVA_INV2] T1 ON T0.DocEntry = T1.DocEntry
	                                    INNER JOIN ODRF T2 ON ((T1.U_Direction = 'I' And T2.ObjType = '13') Or (T1.U_Direction = 'O' And T2.ObjType = '18')) And T1.U_BaseEntry = T2.DocEntry
                                    Where T0.U_Status = 'C' And T0.DocEntry = {0}	
                                    UPDATE [@AVA_OINV] 
                                        SET U_Status = 'D' 
                                            ,U_Comments = ISNULL(U_InvoiceNum,'') + ' 发票已作废' +  Convert(nvarchar(max),GetDate(),21)
                                   Where U_Status = 'C' And DocEntry = {0}
                                  ";
                            SQL = string.Format(SQL, DocEntry, InvoiceNo, Amount);
                            Log.Logger.Writer(SQL);
                            //更新表头的发票号码
                            db.ExceuteNonQuery(SQL);
                            #endregion
                            break;
                    }
                }
                db.EndTranscation(Database.TransactionOptions.Commit);
                db.CloseConnection();
                result.SelectSingleNode("//CODE").InnerText = "0";
                result.SelectSingleNode("//MESSAGE").InnerText = "successful";
                return result.OuterXml;
            }
            catch (Exception ex)
            {
                db.EndTranscation(Database.TransactionOptions.Commit);
                db.CloseConnection();
                result.SelectSingleNode("//CODE").InnerText = "1";
                result.SelectSingleNode("//MESSAGE").InnerText = ex.Message;
                return result.OuterXml;
            }
        }
        /// <summary>
        /// 判断发票是否存在
        /// </summary>
        /// <param name="InvoiceNo"></param>
        /// <returns></returns>
        private bool InvoiceExists(Database.IDatabase db, string InvoiceNo)
        {
            int i = Convert.ToInt32(db.ExceuteScalar("SELECT COUNT('A') FROM [@AVA_OINV] Where DocEntry = " + InvoiceNo));
            if (i <= 0)
                return false;
            return true;
        }
        /// <summary>
        /// 删除发票
        /// </summary>
        /// <param name="DeleteInvoiceXml"></param>
        /// <returns></returns>
        public string DeleteInvoice(string DeleteInvoiceXml)
        {
            string Path = ConfigurationManager.AppSettings["TemplateFilePath"].ToString() + @"\ResultTemplate.xml";
            XmlDocument result = new XmlDocument();
            result.Load(Path);
            #region 异常处理
            if (string.IsNullOrEmpty(DeleteInvoiceXml))
            {
                result.SelectSingleNode("//CODE").InnerText = "1";
                result.SelectSingleNode("//MESSAGE").InnerText = "INCOME_INVOICE_NUM 节点值不存在，请检查金税发送XML数据。";
                return result.OuterXml;
            }
            XmlDocument Invoice = new XmlDocument();
            try
            {
                Invoice.LoadXml(DeleteInvoiceXml);
            }
            catch
            {
                result.SelectSingleNode("//CODE").InnerText = "1";
                result.SelectSingleNode("//MESSAGE").InnerText = "传入的参数数据不是有效的XML格式字符串";
                return result.OuterXml;
            }
            #endregion
            var db = Database.DatabaseFactory.CreateDatabase();
            db.BeginTransaction();
            foreach (XmlNode node in Invoice.SelectNodes("//Item"))
            {
                string DocEntry = node.SelectSingleNode(".//INCOME_INVOICE_NUM").InnerText;
                Log.Logger.Writer("删除单据号：" + DocEntry);
                if (string.IsNullOrEmpty(DocEntry))
                {
                    result.SelectSingleNode("//CODE").InnerText = "1";
                    result.SelectSingleNode("//MESSAGE").InnerText = "INCOME_INVOICE_NUM 节点值不存在，请检查金税发送XML数据。";
                    return result.OuterXml;
                }
                var n = Convert.ToInt32(db.ExceuteScalar("SELECT COUNT('A') FROM [@AVA_OINV] Where DocEntry = " + DocEntry));
                if (n <= 0)
                {
                    result.SelectSingleNode("//CODE").InnerText = "2";
                    result.SelectSingleNode("//MESSAGE").InnerText = DocEntry + " 号码在SAP中不存在，不是有效的单据编码，请检查金税发送的XML数据。";
                    return result.OuterXml;
                }
                string Status = node.SelectSingleNode(".//ORDER_STATU").InnerText;
                string SQL = @"UPDATE T0 SET T0.U_Status = 'P'
                                FROM [@AVA_OINV] T0 
                                    INNER JOIN OCRD T1 ON T0.U_CardCode = T1.CardCode
                                Where T0.U_Status = 'G' And T0.DocEntry = {0}";
                db.ExceuteNonQuery(string.Format(SQL, DocEntry));
            }
            db.EndTranscation(Database.TransactionOptions.Commit);

            result.SelectSingleNode("//CODE").InnerText = "0";
            result.SelectSingleNode("//MESSAGE").InnerText = "successful";
            return result.OuterXml;
        }
    }
}